/*
 * Copyright (C), 2002-2015, 苏宁易购电子商务有限公司
 * FileName: SqlGenerator.java
 * Author:   13071472
 * Date:     2015-9-28 上午9:32:29
 */
package parser.sql;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

import java.io.*;
import java.util.*;

/**
 * SQL生成器.<br>
 * 仅适用于Mysql.
 *
 * @author 13071472
 */
// @SuppressWarnings("serial")
public class SqlGenerator {

    // 文件位置
    // public static final String DOC_PATH =
    // "D:/SVNDoc/中台研发中心文档管理库/库存中心项目群/项目类/库存平台项目/3.系统开发/3.1概要设计/数据存储设计/SN_GAIA_平台库存系统数据库表设计.xlsx";
    public static final String DOC_PATH = "C:\\Users\\17081493\\Desktop\\Excel\\SN_ECIF_账户系统数据库表分库分表设计1.xlsx";

    // dev-SQL脚本生成路径
    // public static final String SQL_PATH =
    // "D:\\svnDoc\\中台研发中心文档管理库\\库存中心项目群\\项目类\\库存平台项目\\3.系统开发\\3.6发布\\04发布文件\\01dev环境\\01DB脚本\\";
    // public static final String SQL_PATH =
    // "E:\\库存中心项目群\\项目类\\库存平台项目\\3.系统开发\\3.6发布\\04发布文件\\SQL一期优化\\01dev环境\\01DB脚本\\";

    // prd-SQL脚本生成路径
    // public static final String SQL_PATH =
    // "D:\\svnDoc\\中台研发中心文档管理库\\库存中心项目群\\项目类\\库存平台项目\\3.系统开发\\3.6发布\\04发布文件\\04prd环境\\01DB脚本\\";
    public static final String SQL_PATH = "C:\\Users\\17081493\\Desktop\\campsql\\";

    // 简单版本:手动指定【单表】
    static List<String> singleLst = new ArrayList<String>() {
        {
        }
    };

    // 仅生成list指定的表(list为空视为生成所有表)
    static List<String> onlyTableList = new ArrayList<String>() {
        {
            add("POINT_ACCOUNT");
            add("COUPON_ACCOUNT");
            add("DEPOSIT_ACCOUNT");
            add("POINT_SUMMARY_ACCOUNT");
            add("POINT_BATCH_LOG");
            add("POINT_BATCH");
            add("COUPON_BATCH");
            add("COUPON_BATCH_LOG");
            add("DEPOSIT_BATCH");
            add("DEPOSIT_BATCH_LOG");
            add("PENDING_ACCOUNT_BATCH");
            add("SUPPLIER_CODE_CUST_NUM_MAPPING");
            add("ACCOUNT_CONFIG");
            add("BRANCH");
            add("STORE");
            add("FROZEN_POINT_BATCH");
            add("FROZEN_POINT_BATCH_LOG");
            add("PE_FROZEN_POINT_SEND");
            add("COMMON_CODE");
            add("MAPPING_RELATION_SHIP");
            add("CUST_ACCOUNT_CHECK");
            add("CUST_ACCOUNT_CHECK_DETAIL");
            add("POINT_LOCK_INFO");

            add("POINT_RATIO_CONFIG");
            add("CURRENSY_RATE");
            add("CAMP_PENDING");
            add("BROADCAST");
            add("EXPIRE_PENDING");

            add("TASK_PENDING");
            add("SP_ETL_LOG");
            add("SEQ_ACCOUNT_BATCH_NUM");


        }
    };

    private static XSSFWorkbook wb;

    public static void main(String[] args) throws IOException {
        int sum = 0;
        Map<String, List<Table>> tableList = SqlGenerator.tableList();
        for (Map.Entry<String, List<Table>> entry : tableList.entrySet()) {
            System.out.println(entry.getKey() + ",表数量:" + entry.getValue().size());
            sum += entry.getValue().size();
            for (Table t : entry.getValue()) {
                System.out.println(t);
            }
        }
        Map<String, List<Column>> tbMap = parseTable();
        System.out.println("总表数:" + sum + ",解析表数:" + tbMap.size());

        for (Map.Entry<String, List<Column>> entry : tbMap.entrySet()) {
            if (dbLocate(entry.getKey(), tableList).equals("NA")) {
                System.out.println(entry.getKey() + "找不到分库.");
            }
        }

        // 字段类型CHECK
        Map<String, Set<String>> typeList = new HashMap<String, Set<String>>();
        for (Map.Entry<String, List<Column>> entry : tbMap.entrySet()) {
            for (Column c : entry.getValue()) {
                Set<String> type = typeList.get(c.getCode());
                if (type == null) {
                    type = new HashSet<String>();
                    typeList.put(c.getCode(), type);
                }
                type.add(c.getType());
            }
        }

        for (Map.Entry<String, Set<String>> entry : typeList.entrySet()) {
            if (entry.getValue().size() > 1) {
                // 同一个字段名称不同字段类型.
                // System.out.println(entry.getKey()+entry.getValue());
            }
        }

        Map<String, Integer> dbSize = new HashMap<String, Integer>();
        Map<String, String> dbAlias = new HashMap<String, String>();

        // dev
        /*
         * dbSize.put("日志库", 2); dbSize.put("核心业务库", 2); dbSize.put("公共库", 1);
         */

        // prd
        dbSize.put("日志库", 1);
        dbSize.put("核心业务库", 1);
        dbSize.put("公共库", 1);

        dbAlias.put("日志库", "LOG");
        dbAlias.put("核心业务库", "BIZ");
        dbAlias.put("公共库", "PUB");

        SqlGenerator.generateSql2(dbSize, dbAlias);
    }

    public static void generateSql2(Map<String, Integer> dbSize, Map<String, String> dbAlias) throws IOException {
        Map<String, List<Table>> tableList = SqlGenerator.tableList();
        Map<String, List<Column>> tbMap = parseTable();

        for (Map.Entry<String, List<Table>> entry : tableList.entrySet()) {
            System.out.println("处理" + entry.getKey());
            // 生成SQL存放的文件夹
            File dir = new File(SQL_PATH, dbAlias.get(entry.getKey()));
            if (!dir.exists() || !dir.isDirectory()) {
                dir.mkdir();
            } else {
                File[] fs = dir.listFiles(new FileFilter() {
                    @Override
                    public boolean accept(File pathname) {
                        return pathname.getName().indexOf("DDL.SQL") > -1;
                    }
                });
                for (File f : fs) {
                    f.delete();
                }
                // dir.mkdir();
            }
            for (Table t : entry.getValue()) {
                if ("Z_DICT".equals(t.getTbName())) {
                    continue;
                }
                System.out.println("处理" + t.getTbName());
                if (!CollectionUtils.isEmpty(onlyTableList) && !onlyTableList.contains(t.getTbName())) {
                    System.out.println("本次不需处理" + t.getTbName());
                    continue;
                }
                if (t.getNumbers() == 1) {
                    // 生成单表CREATE SQL
                    for (int dbNo = 1; dbNo <= dbSize.get(entry.getKey()); dbNo++) {
                        BufferedWriter writer = null;
                        try {
                            writer = new BufferedWriter(new FileWriter(new File(SQL_PATH + dbAlias.get(entry.getKey()),
                                    dbAlias.get(entry.getKey()) + "_" + dbNo + "_DDL.SQL"), true));
                            writer.write(prettyPrintSql(t.getTbName(), tbMap.get(t.getTbName())));
                        } catch (IOException ex) {
                        } finally {
                            try {
                                writer.flush();
                                writer.close();
                            } catch (IOException ex) {
                            }
                        }
                    }
                } else {
                    // FOR LOOP 生成CREATE SQL
                    for (int dbNo = 1; dbNo <= dbSize.get(entry.getKey()); dbNo++) {
                        BufferedWriter writer = null;
                        try {
                            writer = new BufferedWriter(new FileWriter(new File(SQL_PATH + dbAlias.get(entry.getKey()),
                                    dbAlias.get(entry.getKey()) + "_" + dbNo + "_DDL.SQL"), true));
                            for (int tbNo = dbNo - 1; tbNo < t.getNumbers(); tbNo += dbSize.get(entry.getKey())) {
                                writer.write(prettyPrintSql(t.getTbName(), tbMap.get(t.getTbName()), tbNo));
                            }
                        } catch (IOException ex) {
                        } finally {
                            try {
                                writer.flush();
                                writer.close();
                            } catch (IOException ex) {
                            }
                        }
                    }
                }
            }
        }

        // 临时版本

        // BufferedWriter initWriter = new BufferedWriter(new FileWriter(new File(SQL_PATH +
        // "\\PUB\\PUB_1_DB_TABLE_DML.SQL")));
        // String[] prefix = {"T_LOG_","T_BIZ_"};
        // for(int i=0;i<1000;i++){
        // initWriter.write("INSERT INTO Z_DB_TABLE(TABLE_NAME,DB_NO) VALUES ('T_LOG_"+i+"','DB_LOG_"+(i%10+1)+"');\r\n");
        // }
        // for(int i=0;i<1000;i++){
        // initWriter.write("INSERT INTO Z_DB_TABLE(TABLE_NAME,DB_NO) VALUES ('T_BIZ_"+i+"','DB_BIZ_"+(i%10+1)+"');\r\n");
        // }
        // initWriter.flush();
        // initWriter.close();
    }

    // 分库分表定位
    public static String dbLocate(String tbName, Map<String, List<Table>> tableList) {
        for (Map.Entry<String, List<Table>> entry : tableList.entrySet()) {
            for (Table t : entry.getValue()) {
                if (tbName.equals(t.getTbName())) {
                    return entry.getKey();
                }
            }
        }
        return "NA";
    }

    public static Map<String, List<Table>> tableList() {

        Map<String, List<Table>> tableList = new HashMap<String, List<Table>>();
        try {
            FileInputStream stream = new FileInputStream(DOC_PATH);
            if (wb == null) {
                wb = new XSSFWorkbook(stream);
            }
            Sheet sheet = wb.getSheetAt(3);
            int i = 4;
            while (true) {
                Row row = sheet.getRow(i);
                if (row != null && row.getCell(2) != null && row.getCell(3) != null && row.getCell(7) != null
                        && row.getCell(3).getStringCellValue() != null
                        && !row.getCell(3).getStringCellValue().trim().equals("")) {
                    List<Table> tables = tableList.get(row.getCell(2).getStringCellValue());
                    if (tables == null) {
                        tables = new ArrayList<Table>();
                        tableList.put(row.getCell(2).getStringCellValue(), tables);
                    }
                    tables.add(new Table(row.getCell(3).getStringCellValue(), Integer.parseInt(row.getCell(7)
                            .getStringCellValue().substring(0, row.getCell(7).getStringCellValue().lastIndexOf("张")))));
                }
                if (row != null && row.getCell(2) != null
                        && row.getCell(2).getStringCellValue().equals("-------------")) {
                    break;
                }
                i++;
            }
            stream.close();
        } catch (FileNotFoundException ex) {
        } catch (IOException ex) {
        }

        return tableList;
    }

    public static void generateSql(int tbSize, int dbSize, TableLocator tbLocator) {
        // 分库分表定位
        Map<String, List<Column>> tbMap = parseTable();
        // 先处理单表
        BufferedWriter singleWriter = null;
        try {
            singleWriter = new BufferedWriter(new FileWriter(new File(SQL_PATH, "SINGLE_DDL.SQL")));
            for (String tbName : singleLst) {
                singleWriter.write(prettyPrintSql(tbName, tbMap.get(tbName)));
            }
        } catch (IOException ex) {
        } finally {
            try {
                singleWriter.flush();
                singleWriter.close();
            } catch (IOException ex) {
            }
        }
        // 再处理分表;
        for (int dbNo = 1; dbNo <= dbSize; dbNo++) {
            BufferedWriter multiWriter = null;
            try {
                multiWriter = new BufferedWriter(new FileWriter(new File(SQL_PATH, "MULTI_" + dbNo + "_DDL.SQL")));
                for (Map.Entry<String, List<Column>> entry : tbMap.entrySet()) {
                    if (singleLst.contains(entry.getKey())) {
                        continue;
                    }
                    for (int tbNo = dbNo - 1; tbNo < tbSize; tbNo += 4) {
                        multiWriter.write(prettyPrintSql(entry.getKey(), entry.getValue(), tbNo));
                    }
                }
            } catch (IOException ex) {
            } finally {
                try {
                    multiWriter.flush();
                    multiWriter.close();
                } catch (IOException ex) {
                }
            }
        }
    }

    /**
     * 功能描述: 解析EXCEL得到表名--列对应Map<br>
     *
     * @return
     */
    public static Map<String, List<Column>> parseTable() {

        // {TableName}-{TableColumn}
        Map<String, List<Column>> tableMap = new HashMap<String, List<Column>>();

        try {
            FileInputStream stream = new FileInputStream(DOC_PATH);
            if (wb == null) {
                wb = new XSSFWorkbook(stream);
            }
            int sheetCount = wb.getNumberOfSheets();
            for (int i = 6; i < sheetCount; i++) {
                Sheet sheet = wb.getSheetAt(i);
                Cell tbNameCell = sheet.getRow(1).getCell(4);
                // 表名
                String tbName = tbNameCell.toString().trim();

                if ("Z_DICT".equals(tbName)) {
                    continue;
                }

                // 定位ID的位置
                int idIdx = 0;
                for (int k = 0; ; k++) {
                    Row row = sheet.getRow(k);
                    if (row != null && row.getCell(1) != null && "Column Name".equals(row.getCell(1).toString().trim())) {
                        idIdx = k + 1;
                        break;
                    }
                }

                // 从ID开始遍历
                List<Column> lstColumn = new ArrayList<Column>();
                Column column = new Column();
                int j = idIdx;
                Row row = sheet.getRow(j);
                Row pkRow = sheet.getRow(4);
                if (pkRow != null && pkRow.getCell(1).toString().trim().length() != 0) {
                    column.setName(pkRow.getCell(2).toString().trim());
                    column.setType(pkRow.getCell(8).toString().trim());
                }
                lstColumn.add(column);
                while (row != null && row.getCell(1).toString().trim().length() != 0) {
                    column = new Column();
                    column.setCode(row.getCell(1).toString().trim());
                    column.setName(row.getCell(2).toString().trim());
                    column.setType(row.getCell(3).toString().trim());
                    column.setIfNull(row.getCell(4).toString().trim());
                    lstColumn.add(column);
                    j++;
                    row = sheet.getRow(j);
                }
                if (tableMap.get(tbName) != null) {
                    System.out.println(tbName + "已存在,SHEET:" + sheet.getSheetName());
                }
                tableMap.put(tbName, lstColumn);
            }

            stream.close();
        } catch (FileNotFoundException ex) {
        } catch (IOException ex) {
        }

        return tableMap;
    }

    private static String prettyPrintSql(String tbName, List<Column> lstColumn) {
        StringBuffer sBuffer = new StringBuffer();
        // DROP TABLE IF EXISTS
        sBuffer.append("DROP TABLE IF EXISTS ").append(tbName).append(";\n");
        sBuffer.append("CREATE TABLE ").append(tbName).append("\n");
        sBuffer.append("(\n");
        for (int i = 1; i < lstColumn.size(); i++) {
            Column column = lstColumn.get(i);
            if (column.getCode().equals("ID") && column.getType().toUpperCase().endsWith("INT")) {
                column.setType("bigint(20) unsigned NOT NULL AUTO_INCREMENT");
            } else if (tbName.toUpperCase().equals("Z_INV_QTY_SERIAL_SYNC_LOG")) {
                if (column.getCode().equals("SYNC_NUM")) {
                    column.setType("INT DEFAULT'0'");
                }
            }
            sBuffer.append("        ").append(column.toString()).append("\n");
        }
        Column column = lstColumn.get(0);
        if ("PK".equals(column.getType())) {
            sBuffer.append("        ").append("PRIMARY KEY (" + column.getName() + ")").append("\n");
        } else {
            if (",".equals(String.valueOf(sBuffer.charAt(sBuffer.length() - 2)))) {
                sBuffer = (new StringBuffer(sBuffer.substring(0, sBuffer.length() - 2)).append("\n"));
            }
        }
        sBuffer.append(");\n");
        return sBuffer.toString();
    }

    private static String prettyPrintSql(String tbName, List<Column> lstColumn, int tbNo) {
        StringBuffer sBuffer = new StringBuffer();
        // DROP TABLE IF EXISTS
        sBuffer.append("DROP TABLE IF EXISTS ").append(tbName).append("_").append(tbNo).append(";\n");
        sBuffer.append("CREATE TABLE ").append(tbName).append("_").append(tbNo).append("\n");
        sBuffer.append("(\n");
        for (int i = 1; i < lstColumn.size(); i++) {
            Column column = lstColumn.get(i);
            if (column.getCode().equals("ID") && column.getType().toUpperCase().startsWith("INT")) {
                column.setType("INT AUTO_INCREMENT");
            } else if (column.getCode().equals("ID") && column.getType().toUpperCase().startsWith("BIGINT")) {
                column.setType("BIGINT AUTO_INCREMENT");
            } else if (tbName.toUpperCase().equals("Z_INSTOCK_QTY")) {
                if (column.getCode().equals("INSTOCK_QTY") || column.getCode().equals("HOPE1_QTY")
                        || column.getCode().equals("HOPE2_QTY")) {
                    column.setType("DECIMAL(16,3) DEFAULT'0.000'");
                }
            } else if (tbName.toUpperCase().equals("Z_TRANSIT_IN_QTY")) {
                if (column.getCode().equals("PO_IN_QTY") || column.getCode().equals("STO_IN_QTY")
                        || column.getCode().equals("RV_IN_QTY") || column.getCode().equals("HOPE1_QTY")
                        || column.getCode().equals("HOPE2_QTY")) {
                    column.setType("DECIMAL(16,3) DEFAULT'0.000'");
                }
            } else if (tbName.toUpperCase().equals("Z_TRANSIT_OUT_QTY")) {
                if (column.getCode().equals("SO_LOCK_QTY") || column.getCode().equals("DN_LOCK_QTY")
                        || column.getCode().equals("PO_OUT_QTY") || column.getCode().equals("STO_OUT_QTY")
                        || column.getCode().equals("RV_OUT_QTY") || column.getCode().equals("ACTIVITY_RV_QTY")
                        || column.getCode().equals("TMALL_RV_QTY") || column.getCode().equals("HOPE1_QTY")
                        || column.getCode().equals("HOPE2_QTY")) {
                    column.setType("DECIMAL(16,3) DEFAULT'0.000'");
                }
            } else if (tbName.toUpperCase().equals("Z_INV_STATUS")) {
                if (column.getCode().equals("PROTOTYPE_FLAG")) {
                    column.setType("VARCHAR(1) DEFAULT'N'");
                }
            } else if (tbName.toUpperCase().equals("Z_INV_QTY_SERIAL")) {
                if (column.getCode().equals("DAILY_CALCULATED")) {
                    column.setType("VARCHAR(1) DEFAULT'N'");
                }
            } else if (tbName.toUpperCase().equals("Z_INV_QTY_SERIAL_DAY")) {
                if (column.getCode().equals("SYNC_FLAG")) {
                    column.setType("VARCHAR(1) DEFAULT'N'");
                }
            }

            sBuffer.append("        ").append(column.toString()).append("\n");
        }
        Column column = lstColumn.get(0);

        if ("PK".equals(column.getType())) {
            sBuffer.append("        ").append("PRIMARY KEY (" + column.getName() + ")").append("\n");
        } else {
            if (",".equals(String.valueOf(sBuffer.charAt(sBuffer.length() - 2)))) {
                sBuffer = (new StringBuffer(sBuffer.substring(0, sBuffer.length() - 2)).append("\n"));
            }
        }
        sBuffer.append(");\n");
        return sBuffer.toString();
    }

    static class Column {
        String code;
        String name;
        String type;
        String ifNull;

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            this.code = code;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getType() {
            return type;
        }

        public void setType(String type) {
            this.type = type;
        }

        public String getIfNull() {
            return ifNull;
        }

        public void setIfNull(String ifNull) {
            this.ifNull = ifNull;
        }

        @Override
        public String toString() {

            String columnStr = "";

//            if (code.equals("CREATE_TIME")) {
//                columnStr = "CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP" + " COMMENT '" + name + "',";
//            }
//            else if (code.equals("UPDATE_TIME")) {
//                columnStr = code.toUpperCase() + " TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00'" + " COMMENT '" + name
//                        + "',";
//            }
//            else
            if (ifNull.equals("N") && !code.equals("ID")) {
                columnStr = code.toUpperCase() + " " + type.toUpperCase() + " NOT NULL" + " COMMENT '" + name + "',";
            } else {
                columnStr = code.toUpperCase() + " " + type.toUpperCase() + " COMMENT '" + name + "',";
            }

            return columnStr;
        }

    }

    static class Table {
        // 表名
        String tbName;
        // 分表数
        int numbers;

        public String getTbName() {
            return tbName;
        }

        public void setTbName(String tbName) {
            this.tbName = tbName;
        }

        public int getNumbers() {
            return numbers;
        }

        public void setNumbers(int numbers) {
            this.numbers = numbers;
        }

        public Table(String tbName, int numbers) {
            this.tbName = tbName;
            this.numbers = numbers;
        }

        @Override
        public String toString() {
            return "Table [tbName=" + tbName + ", numbers=" + numbers + "]";
        }

    }

    static interface TableLocator {
        int locateByNo(int tbNo);
    }

    public static XSSFWorkbook getWb() {
        return wb;
    }

}
